/**
*
* Tool to do queries efficiently
*
* Project: idbCrownInServer
* Author:  TM
* Date:    01/03/2013
*
* History
* ------------------
* 01/03/2013 - TM - Initial version.
*/
 
package DataModel;

import java.sql.*;
import java.util.*;

import Application.*;
import Util.*;

public class libSQLUtil
{
  /** Debug key */
  protected static final String DEBUG_KEY = "SQL_UTIL";
  /** Database connection - if NULL, getMainConnection() is assumed */
  protected libDatabaseConnection connection;
  /** Application support */
  protected static libApplication appSupport;
  
  
  /** Constructor - intended to create singleton */
  protected libSQLUtil()
  {
    this(libDatabaseConnection.getMainConnection());
  }
  
  /** Constructor with connection */
  public libSQLUtil(libDatabaseConnection connection)
  {
    appSupport      = libApplication.getInstance();
    this.connection = connection;
    try
    {
      connection.connect();
    }
    catch(Exception ex)
    {
      appSupport.showErrorMsg(ex);
    }
  }
  
  /** Database connection */
  protected libDatabaseConnection getConnection()
  {
    return connection != null ? connection : libDatabaseConnection.getMainConnection();
  }
  
  /** Select rows using given ?-SQL and parameters - 
      returns Object[][] = one Object[] for each row - NULL if error 
    */
  public Object[][] Select(String sql, Object[] params) throws SQLException
  {
    Object[][] result = null;
    try
    {
      List rows = selectImpl(sql, params);
      result = rows != null ? (Object[][])rows.toArray(new Object[rows.size()][]) : null;
    }
    finally
    {
      debug("select(" + sql + "," + dumpParams(params) +")=" + (result!= null ? result.length : -1));
    }
    return result;
  }
    
  /** Select one row using given ?-SQL and parameters - returns Object[], NULL if error */
  public Object[] SelectOne(String sql, Object params[]) throws SQLException
  {
    Object[] result = null;
    try
    {
      List rows = selectImpl(sql, params);
      result = rows != null && rows.size() == 1 ? getRowFrom(rows, 0) : null;
    }
    finally
    {
      debug("selectOne(" + sql + "," + dumpParams(params) + ")=" + dumpResult(result));
    }
    return result;
  }

  /** Select single column rows using given ?-SQL and parameters - 
      returns Object[] = one Object for each row - NULL if error
    */
  public Object[] SelectSingle(String sql, Object[] params) throws SQLException
  {
    Object[] result = null;
    try
    {
      List rows = selectImpl(sql, params);
      if (rows != null)
      {
        int count = rows.size();;
        if (count == 0 || getRowFrom(rows, 0).length == 1)
        {
          result = new Object[count];
          for (int i=0; i<count; i++)
            result[i] = getRowFrom(rows, i)[0];
        }  
      }
    }
    finally
    {
      debug("selectSingle(" + sql + "," + dumpParams(params) +")=" + dumpResult(result));
    }
    return result;
  }
  
  /** Select a one row single column using given ?-SQL and parameters - returns NULL if error */
  public Object SelectSingleOne(String sql, Object[] params) throws SQLException
  {
    Object result = null;
    try
    {
      List rows = selectImpl(sql, params);
      if (rows != null && rows.size() == 1)
      { 
        Object[] row = getRowFrom(rows, 0);
        if (row.length == 1)
          result = row[0];
      }  
    }
    finally
    {
      debug("selectSingleOne(" + sql + "," + dumpParams(params) +")=" + result);
    }
    return result;
  }
  
  /** Delete using given ?-SQL and parameters - returns number of delete rows (-1 in case of error) 
      Assumes it is run inside DB transaction
    */
  public int Delete(String sql, Object[] params) throws SQLException
  {
    int count = -1;
    libDatabaseConnection con = getConnection();
    PreparedStatement pstmt = (PreparedStatement)con.createStatement(sql);
    try
    {
      fillStatement(pstmt, params);
      count = pstmt.executeUpdate();
    }
    finally
    {
      con.closeStatement(pstmt);
      debug("delete(" + sql + "," + dumpParams(params) +")=" + count);
    }
    return count;
  }

  /** Update using given ?-SQL and parameters - returns number of update rows (-1 in case of error) 
      Assumes it is run inside DB transaction
    */
  public int Update(String sql) throws SQLException
  {
    int count = -1;
    libDatabaseConnection con = getConnection();
    PreparedStatement pstmt = (PreparedStatement)con.createStatement(sql);
    try
    {
      count = pstmt.executeUpdate();
    }
    finally
    {
      con.closeStatement(pstmt);
      debug("update(" + sql + ")=" + count);
    }
    return count;
  }


  /** Inserts using given ?-SQL and parameters - returns number of inserted rows  */
  public int Insert(dmCustom dm, libField[] fields) throws SQLException
  {
    int result = -1;
    int      numFields = fields.length;
    Object[] paramVal  = new Object[numFields];
    String   sql = "";
    
    libDatabaseConnection con = getConnection();
    con.startExclusiveAccess();
    
    try 
    {
  	  //Add the data into the database
  	  sql = "insert into "+dm.getTableName()+
                         " ("+getFieldNames(fields)+
                         ") values ("+getQuestionFields(numFields)+")";
  	  PreparedStatement pst = (PreparedStatement)(con.createStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS));
     
  	  for(int i=0; i<numFields; i++)
  	  {
       fields  [i].assignValue(i + 1, pst);
       paramVal[i] = fields[i].getValue();
  	  }
     
  	  try
  	  {
  	    int numRowsChanged = pst.executeUpdate();
  	    debug("Filas insertadas: "+numRowsChanged);
       
       if (numRowsChanged == 1)
       {
         ResultSet rs = pst.getGeneratedKeys();
         while (rs.next()) 
         {
             result = rs.getInt(1);
             debug("Clave generada = " + result);
         }

       }
  	  }
  	  finally
  	  {
  	    con.closeStatement(pst);
  	  }
    }
    catch(Exception e)
    {
  	    debug("Error insertando registro: " + e.getMessage());
    }
    finally
    {
       con.endExclusiveAccess();    
    }
    debug("QUERY_INSERT:"+sql+" -> ("+dumpParams(paramVal)+")");
    return result;
  }

  /** Get the list of fieldNames prepared to do the insert in a single string */
  protected String getFieldNames(libField[] fields)
  {
     int    numFields = fields.length;
     String result    = "";
     for(int i=0;i<(numFields - 1);i++)
       result += fields[i].getName() + ",";
     
     result += fields[numFields - 1].getName();
     
     return result;
  }

  /** Get the list of ? characters in a single string */
  protected String getQuestionFields(int numFields)
  {
     String result = "";
     for(int i=0;i<(numFields - 1);i++)
       result += "?" + ",";
     
     result += "?";
     
     return result;
  }
 
  /** Select rows using given ?-SQL and parameters */
  protected List selectImpl(String sql, Object[] params) throws SQLException
  {
    List result = null;
    libDatabaseConnection con = getConnection();
    con.startExclusiveAccess();
    try
    {
      PreparedStatement pstmt = (PreparedStatement)con.createStatement(sql);
      try
      {
        result = getRowsFor(pstmt, params);
      }
      finally
      {
        con.closeStatement(pstmt);
      }
    }
    finally
    {
      con.endExclusiveAccess();
    }
    return result;
  }
  
  /** Get column names for a query */
  public String[] getColumnNames(String sql) throws SQLException
  {
     String[] columnNames = null;
     libDatabaseConnection con = getConnection();
     con.startExclusiveAccess();
     try
     {
       PreparedStatement pstmt    = (PreparedStatement)con.createStatement(sql);
       ResultSetMetaData metaData = pstmt   .getMetaData();
       int numberOfColumns        = metaData.getColumnCount();
       
       columnNames = new String[numberOfColumns];
       for (int i = 1; i < numberOfColumns + 1; i++) 
         columnNames[i] = metaData.getColumnName(i);
     }
     finally
     {
       con.endExclusiveAccess();
     }
     
     return columnNames;
  }
  
  
  /** Get selected rows for given PreparedStatement & its parameters */
  protected List getRowsFor(PreparedStatement pstmt, Object[] params) throws SQLException
  {
    List result;
    if (params != null)
      fillStatement(pstmt, params);
    ResultSet rset = pstmt.executeQuery();
    try
    {
      result = getRowsFrom(rset);
    }
    finally
    {
      rset.close();
    }
    return result;
  }

  /** Fill PreparedStatement with given parameters */
  public void fillStatement(PreparedStatement pstmt, Object[] params) throws SQLException
  {
    for (int i=0; i<params.length; i++)
    {
      Object param = params[i];
      if      (param instanceof Timestamp)
        setTimestamp(pstmt, i+1, (Timestamp)param);
      else if (param instanceof Double)
        setDouble   (pstmt, i+1, (Double)   param);
      else if (param instanceof Boolean)
        setBoolean  (pstmt, i+1, (Boolean)  param);
      else
        pstmt.setObject(i+1, param);
    }  
  }
  
  /** Get selected rows as List of Object[] */
  protected List getRowsFrom(ResultSet rset) throws SQLException
  {
    int[] colTypes = getColTypesFrom(rset);
    List rows = new ArrayList();
    while (rset.next())
      rows.add(getRowFrom(rset, colTypes));
    return rows;
  }
  
  /** Get single row as Object[] */
  protected Object[] getRowFrom(ResultSet rset, int[] colTypes) throws SQLException
  {
    int colCount = colTypes.length;
    Object[] row = new Object[colCount];
    for (int i=0; i<colCount; i++)
      if (colTypes[i] == Types.TIMESTAMP
       || colTypes[i] == Types.DATE)
        row[i] = getTimestamp(rset, i+1);
      else
        row[i] = rset.getObject(i+1);
    return row;
  }
  
  /** Column types */
  protected int[] getColTypesFrom(ResultSet rset) throws SQLException
  {
    ResultSetMetaData metaData = rset.getMetaData();
    int   colCount = metaData.getColumnCount();
    int[] colTypes = new int[colCount];
    for (int i=0; i<colCount; i++)
      colTypes[i] = metaData.getColumnType(i+1);
    return colTypes;
  }
 
  /** Row from list */
  protected Object[] getRowFrom(List rows, int row)
  {
    return rows != null && row < rows.size() ? (Object[])rows.get(row) : null;
  }
  
  //
  // Timestamp utilities
  //
  /** Set time stamp into PreparedStatement */
  protected void setTimestamp(PreparedStatement pstmt, int column, Timestamp value) 
            throws SQLException
  {
    pstmt.setTimestamp(column, value, getDBTZ());
  }
  
  /** Get time stamp from ReultSet - copied from libDateField.getTimestampValue() */
  protected Timestamp getTimestamp(ResultSet rset, int column) throws SQLException
  {
    Timestamp value = null;
    try
    {
      value = rset.getTimestamp(column, getDBTZ());
    }
    catch (SQLException e)
    {  // MSSQL JDBC driver thows exception when value is NULL - catch and retry to get NULL
      value = (Timestamp)rset.getObject(column);
      if (value != null)        // If value is not NULL then SQLException was true exception
        throw e;
    }    
    return value;
  }
  
  /** Obtain TZ used to record dates in DB - Calendar format is given */
  protected Calendar getDBTZ()
  {
    return getConnection().getDbDateFormat().getCalendar();
  }
  
  //
  // Number/boolean utilities
  // Transform row column objects to given number/boolean types
  // (taken from libXXXField.castValue()
  // String & Timestamp must be simply casted
  //
  /** Get row column object as Boolean */
  public Boolean GetAsBoolean(Object value)
  {
    Boolean result = null;
    if (value != null)
    {
      int noValue = getConnection().getDbFalseConst().intValue();
      result = ((Number)value).intValue() != noValue ? Boolean.TRUE : Boolean.FALSE;
    }
    return result;
  }

  /** Get row column object as Integer */
  public Integer GetAsInteger(Object value)
  {
    return value == null || value instanceof Integer ? (Integer)value 
                                                     : new Integer(((Number)value).intValue());
  }

  /** Get row column object as Long */
  public Long GetAsLong(Object value)
  {
    return value == null || value instanceof Long    ? (Long)   value 
                                                     : new Long   (((Number)value).longValue());
  }

  /** Get row column object as Double */
  public Double GetAsDouble(Object value)
  {
    return value != null 
         ? (value instanceof Double  ? (Double)value : new Double(((Number)value).doubleValue()))
         : null;
  }
  
  /** Set booelan (standard connection representation) into PreparedStatement */
  protected void setBoolean(PreparedStatement pstmt, int column, Boolean value) throws SQLException            
  {
    boolean v = value.booleanValue();
    libDatabaseConnection con = getConnection();
    pstmt.setObject(column, v ? con.getDbTrueConst() : con.getDbFalseConst());
  }
  
  /** Set double into PreparedStatement */
  protected void setDouble(PreparedStatement pstmt, int column, Double value) 
            throws SQLException
  {
    pstmt.setObject(column, value);
  }
  
      
  //
  // Debug tools
  //
  /** Send mesage to debug output */
  protected void debug(String msg)
  {
    libFileDebug.getInstance().write("libSQLUtil" + dumpConnection() + '.' + msg);
  }

  /** Dump connection - if NULL (meaning getMainConnection()) it returns "" */
  protected String dumpConnection()
  {
    return connection != null ? ("[" + connection.getName() + "]"): "";
  }
  
  /** Dump parameter & result arrays */
  protected String dumpParams(Object[] params) { return dumpArray(params); }
  protected String dumpResult(Object[] result) { return dumpArray(result); }
  protected String dumpArray (Object[] result)
  {
    String s = null;
    if (result != null)
    {
      StringBuffer buf = new StringBuffer("[");
      for (int i=0; i<result.length; i++)
        buf.append(result[i]).append(',');
      int len = buf.length();
      if (len > 1)
        buf.setCharAt(len-1, ']');
      else
        buf.append(']');  // Empty array  
      s = buf.toString();  
    }
    return s;
  }
  
  /** Dump first row length */
  protected int firstRowLength(List list)
  {
    Object[] row = getRowFrom(list, 0);
    return row != null ? row.length : -1;
  }
  
  //
  // Static support
  //
  /** Singleton instance */
  protected static libSQLUtil instance;
  
  /** Singleton instance */
  public static synchronized libSQLUtil getInstance()
  {
    if (instance == null)
      instance = new libSQLUtil();
    return instance;  
  }
  
  /** Select rows using given ?-SQL and parameters - 
      returns Object[][] = one Object[] for each row - NULL if error 
    */
  public static Object[][] select(String sql, Object[] params) throws SQLException
  {
    return getInstance().Select(sql, params);
  }
    
  /** Select one row using given ?-SQL and parameters - returns Object[], NULL if error */
  public static Object[] selectOne(String sql, Object params[]) throws SQLException
  {
    return getInstance().SelectOne(sql, params);
  }

  /** Select single column rows using given ?-SQL and parameters - 
      returns Object[] = one Object for each row - NULL if error
    */
  public static Object[] selectSingle(String sql, Object[] params) throws SQLException
  {
    return getInstance().SelectSingle(sql, params);
  }
  
  /** Select a one row single column using given ?-SQL and parameters - returns NULL if error */
  public static Object selectSingleOne(String sql, Object[] params) throws SQLException
  {
    return getInstance().SelectSingleOne(sql, params);
  }
  
  /** Delete using given ?-SQL and parameters - returns number of delete rows (-1 in case of error) 
      Assumes it is run inside DB transaction
    */
  public static int delete(String sql, Object[] params) throws SQLException
  {
    return getInstance().Delete(sql, params);
  }
  
  //
  // Number/boolean utilities
  // Transform row column objects to given number/boolean types
  // (taken from libXXXField.castValue()
  // String & Timestamp must be simply casted
  //
  /** Get row column object as Boolean */
  public static Boolean getAsBoolean(Object value)
  {
    return getInstance().GetAsBoolean(value);
  }

  /** Get row column object as Integer */
  public static Integer getAsInteger(Object value)
  {
    return getInstance().GetAsInteger(value);
  }

  /** Get row column object as Long */
  public static Long getAsLong(Object value)
  {
    return getInstance().GetAsLong(value);
  }

  /** Get row column object as Double */
  public static Double getAsDouble(Object value)
  {
    return getInstance().GetAsDouble(value);
  }
  
  //
  // Convenient methods with 0, 1, 2 & 3 parameters
  //
  /** select() */
  public static Object[][] select(String sql) throws SQLException
  {
    return select(sql, new Object[] { });
  }
  public static Object[][] select(String sql, Object param1) throws SQLException
  {
    return select(sql, new Object[] { param1 });
  }
  public static Object[][] select(String sql, Object param1, Object param2) throws SQLException
  {
    return select(sql, new Object[] { param1, param2 });
  }
  public static Object[][] select(String sql, Object param1, Object param2, Object param3) 
         throws SQLException
  {
    return select(sql, new Object[] { param1, param2, param3 });
  }
  /** selectOne() */
  public static Object[] selectOne(String sql) throws SQLException
  {
    return selectOne(sql, new Object[] { });
  }
  public static Object[] selectOne(String sql, Object param1) throws SQLException
  {
    return selectOne(sql, new Object[] { param1 });
  }
  public static Object[] selectOne(String sql, Object param1, Object param2) throws SQLException
  {
    return selectOne(sql, new Object[] { param1, param2 });
  }
  public static Object[] selectOne(String sql, Object param1, Object param2, Object param3) 
         throws SQLException
  {
    return selectOne(sql, new Object[] { param1, param2, param3 });
  }
  /** selectSingle() */
  public static Object[] selectSingle(String sql) throws SQLException
  {
    return selectSingle(sql, new Object[] { });
  }
  public static Object[] selectSingle(String sql, Object param1) throws SQLException
  {
    return selectSingle(sql, new Object[] { param1 });
  }
  public static Object[] selectSingle(String sql, Object param1, Object param2) throws SQLException
  {
    return selectSingle(sql, new Object[] { param1, param2 });
  }
  public static Object[] selectSingle(String sql, Object param1, Object param2, Object param3) 
         throws SQLException
  {
    return selectSingle(sql, new Object[] { param1, param2, param3 });
  }
  /** selectSingleOne() */
  public static Object selectSingleOne(String sql) throws SQLException
  {
    return selectSingleOne(sql, new Object[] { });
  }
  public static Object selectSingleOne(String sql, Object param1) throws SQLException
  {
    return selectSingleOne(sql, new Object[] { param1 });
  }
  public static Object selectSingleOne(String sql, Object param1, Object param2) throws SQLException
  {
    return selectSingleOne(sql, new Object[] { param1, param2 });
  }
  public static Object selectSingleOne(String sql, Object param1, Object param2, Object param3) 
         throws SQLException
  {
    return selectSingleOne(sql, new Object[] { param1, param2, param3 });
  }
  /** delete() */
  public static int delete(String sql) throws SQLException
  {
    return delete(sql, new Object[] { });
  }
  public static int delete(String sql, Object param1) throws SQLException
  {
    return delete(sql, new Object[] { param1 });
  }
  public static int delete(String sql, Object param1, Object param2) throws SQLException
  {
    return delete(sql, new Object[] { param1, param2 });
  }
  public static int delete(String sql, Object param1, Object param2, Object param3) throws SQLException
  {
    return delete(sql, new Object[] { param1, param2, param3 });
  }
  
}
